package com.henu.service;

import java.util.List;

import org.smart4j.framework.dao.DatabaseHelper;
import org.smart4j.framework.tx.annotation.Service;


@Service
public class ProductInfoService {

    /**
     * 关键字搜索
     * @return
     */
    public List findByKey(String key) {
        List result = DatabaseHelper.queryMapList(
                "SELECT * from productinfo t where t.name like ? and t.status='1'", "%" + key + "%");
        return result;
    }

    public List findByCategory(int categoryid) {
        return DatabaseHelper.queryMapList(
                "SELECT * FROM productinfo T WHERE T.categoryID = ? and t.status = '1' order by id asc",
                categoryid);
    }

    public List findByProductIdMore(int productid) {
        return DatabaseHelper.queryMapList(
                "SELECT * FROM PRODUCTINFO T WHERE T.id>? and t.status = '1' order by id asc", productid);
    }

    public Object findByProductId(int productid) {
        List result = DatabaseHelper.queryMapList(
                        "SELECT T.*,c.categoryName FROM PRODUCTINFO T,CATEGORY C WHERE T.categoryID=C.ID AND T.id = ? and t.status = '1' order by id asc",
                        productid);
        System.out.println(result);
        if (result != null && result.size() != 0) {
            return result.get(0);
        }
        return null;
    }

    public List getStar() {
        String sql = "select t.*,ifnull(aa.avgStar,0) avgStar,c.categoryName from category c, productinfo t left join (select a.productID,AVG(a.star) avgStar from appraise a GROUP BY a.productID) aa on t.id=aa.productID  where t.categoryID=c.id order by aa.avgStar desc";
        return DatabaseHelper.queryMapList(sql);
    }

}
